package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.OverseaDnSearchDTO;
import com.sunda.spmsoversea.entity.OverseaDn;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 海外仓交货单表(空运人带/转储/本地采购) Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-12-14
 */
@Mapper
public interface OverseaDnMapper extends BaseMapper<OverseaDn> {

    /** 获取转储交货单表头分页列表 */
    @Select("<script>" +
            "SELECT D.UUID_DELIVERY_NOTE AS \"uuidDeliveryNote\",\n" +
            "       D.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       D.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       D.WERKS_RECEIVE AS \"werksReceive\",\n" +
            "       D.WHS_LOCATION_CODE_RECEIVE AS \"whsLocationCodeReceive\",\n" +
            "       D.SAP_CREATE_DATE AS \"sapCreateDate\",\n" +
            "       D.DELIVERY_DATE AS \"deliveryDate\",\n" +
            "       D.ARRIVAL_DATE AS \"arrivalDate\",\n" +
            "       D.REMARKS AS \"remarks\",\n" +
            "       D.COMMENTS AS \"comments\",\n" +
            "       RE.SAP_PURCHASE_ORDER AS \"sapPurchaseOrder\",\n" +
            "       RE.WERKS_DELIVERY AS \"werksDelivery\",\n" +
            "       RE.SUPPLIER_CODE AS \"supplierCode\",\n" +
            "       RE.CREATE_USER AS \"createUser\",\n" +
            "       D.POSTING_DATE AS \"postingDate\",\n" +
            "       D.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       D.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       D.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       D.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       D.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       D.UNDER_VOUCHER_NUMBER AS \"underVoucherNumber\",\n" +
            "       D.UNDER_VOUCHER_YEAR AS \"underVoucherYear\",\n" +
            "       D.UNDER_POSTING_DATE AS \"underPostingDate\",\n" +
            "       D.CANCEL_UNDER_POSTING_DATE AS \"cancelUnderPosting\",\n" +
            "       D.CANCEL_UNDER_VOUCHER_NUMBER AS \"cancelUnderVoucherNumber\",\n" +
            "       D.COST_CENTER AS \"costCenter\",\n" +
            "       D.DATA_VERSION AS \"dataVersion\",\n" +
            "       D.BUSINESS_TYPE AS \"businessType\"\n" +
            "FROM OVERSEA_DN D\n" +
            "    LEFT JOIN (\n" +
            "        SELECT DISTINCT DD.SAP_DELIVERY_NOTE, DD.WERKS_DELIVERY,\n" +
            "               PO.SAP_PURCHASE_ORDER, PO.SUPPLIER_CODE, PO.CREATE_USER\n" +
            "        FROM OVERSEA_DN_DTL DD\n" +
            "            LEFT JOIN OVERSEA_PURCHASE_ORDER PO ON DD.SAP_PURCHASE_ORDER = PO.SAP_PURCHASE_ORDER\n" +
            "        GROUP BY DD.SAP_DELIVERY_NOTE, DD.WERKS_DELIVERY, PO.SAP_PURCHASE_ORDER, PO.SUPPLIER_CODE, PO.CREATE_USER\n" +
            "        ) RE ON D.SAP_DELIVERY_NOTE = RE.SAP_DELIVERY_NOTE\n" +
            "WHERE 1=1 \n" +
            "  <when test='dto.sapDeliveryNote!=null and dto.sapDeliveryNote != \"\"'> AND D.SAP_DELIVERY_NOTE = #{dto.sapDeliveryNote}</when>" +
            "  <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND D.SAP_DELIVERY_NOTE = #{dto.spmsStatus}</when>" +
            "  <when test='dto.werksDelivery!=null and dto.werksDelivery != \"\"'> AND RE.WERKS_DELIVERY = #{dto.werksDelivery}</when>" +
            "  <when test='dto.werksReceive!=null and dto.werksReceive != \"\"'> AND D.WERKS_RECEIVE = #{dto.werksReceive}</when>" +
            "  <when test='dto.businessType!=null and dto.businessType != \"\"'> AND D.BUSINESS_TYPE = #{dto.businessType}</when>" +
            "  <when test='dto.deliveryDateBegin!=null and dto.deliveryDateBegin != \"\"'> AND TO_DATE(D.DELIVERY_DATE, 'yyyyMMdd') &gt;= TO_DATE(#{dto.deliveryDateBegin}, 'yyyy-MM-dd') </when>" +
            "  <when test='dto.deliveryDateEnd!=null and dto.deliveryDateEnd != \"\"'> AND TO_DATE(D.DELIVERY_DATE, 'yyyyMMdd') &lt;= TO_DATE(#{dto.deliveryDateEnd}, 'yyyy-MM-dd') </when>" +
            "  ORDER BY D.UPDATE_TIME DESC, D.SPMS_STATUS ASC, D.SAP_DELIVERY_NOTE ASC " +
            "</script>")
    List<Map<String, Object>> getDnPage(IPage page, @Param("dto") OverseaDnSearchDTO overseaDnSearchDTO);

    @Select("SELECT D.UUID_DELIVERY_NOTE AS \"uuidDeliveryNote\",\n" +
            "       D.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       D.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       D.WERKS_RECEIVE AS \"werksReceive\",\n" +
            "       D.WHS_LOCATION_CODE_RECEIVE AS \"whsLocationCodeReceive\",\n" +
            "       D.SAP_CREATE_DATE AS \"sapCreateDate\",\n" +
            "       D.DELIVERY_DATE AS \"deliveryDate\",\n" +
            "       D.ARRIVAL_DATE AS \"arrivalDate\",\n" +
            "       D.REMARKS AS \"remarks\",\n" +
            "       D.COMMENTS AS \"comments\",\n" +
            "       RE.SAP_PURCHASE_ORDER AS \"sapPurchaseOrder\",\n" +
            "       RE.WERKS_DELIVERY AS \"werksDelivery\",\n" +
            "       RE.SUPPLIER_CODE AS \"supplierCode\",\n" +
            "       RE.CREATE_USER AS \"createUser\",\n" +
            "       D.POSTING_DATE AS \"postingDate\",\n" +
            "       D.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       D.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       D.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       D.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       D.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       D.UNDER_VOUCHER_NUMBER AS \"underVoucherNumber\",\n" +
            "       D.UNDER_VOUCHER_YEAR AS \"underVoucherYear\",\n" +
            "       D.UNDER_POSTING_DATE AS \"underPostingDate\",\n" +
            "       D.CANCEL_UNDER_POSTING_DATE AS \"cancelUnderPosting\",\n" +
            "       D.CANCEL_UNDER_VOUCHER_NUMBER AS \"cancelUnderVoucherNumber\",\n" +
            "       D.COST_CENTER AS \"costCenter\",\n" +
            "       D.DATA_VERSION AS \"dataVersion\",\n" +
            "       D.BUSINESS_TYPE AS \"businessType\"\n" +
            "FROM OVERSEA_DN D\n" +
            "    LEFT JOIN (\n" +
            "        SELECT ROWNUM, DD.SAP_DELIVERY_NOTE, DD.WERKS_DELIVERY,\n" +
            "               PO.SAP_PURCHASE_ORDER, PO.SUPPLIER_CODE, PO.CREATE_USER\n" +
            "        FROM OVERSEA_DN_DTL DD\n" +
            "            LEFT JOIN OVERSEA_PURCHASE_ORDER PO ON DD.SAP_PURCHASE_ORDER = PO.SAP_PURCHASE_ORDER\n" +
            "        WHERE ROWNUM = 1 AND DD.SAP_DELIVERY_NOTE = #{sapDeliveryNote}\n" +
            "        ) RE ON D.SAP_DELIVERY_NOTE = RE.SAP_DELIVERY_NOTE\n" +
            "WHERE D.SAP_DELIVERY_NOTE = #{sapDeliveryNote} ")
    Map<String, Object> getDnInfo(@Param("sapDeliveryNote") String sapDeliveryNote);

    /** 转储收货，按应收收货提交SAP表头结构 */
    @Select("SELECT OD.SAP_DELIVERY_NOTE AS \"xblnr\",\n" +
            "       OD.SAP_DELIVERY_NOTE AS \"vbelnIm\",\n" +
            "       REPLACE(OD.ARRIVAL_DATE, '-', '') AS \"bldat\",\n" +
            "       REPLACE(#{postingDate}, '-', '') AS \"budat\",\n" +
            "       #{userId} AS \"createdby\",\n" +
            "       '101' AS \"bwart\",\n" +
            "       OD.REMARKS AS \"bktxt\",\n" +
            "       RE.SAP_PURCHASE_ORDER AS \"ebeln\"\n" +
            "FROM OVERSEA_DN OD\n" +
            "    LEFT JOIN (\n" +
            "        SELECT ROWNUM, ODD.SAP_PURCHASE_ORDER,\n" +
            "               ODD.SAP_DELIVERY_NOTE\n" +
            "        FROM OVERSEA_DN_DTL ODD\n" +
            "        WHERE ROWNUM = 1\n" +
            "          AND ODD.SAP_DELIVERY_NOTE = #{sapDeliveryNote})\n" +
            "        RE ON OD.SAP_DELIVERY_NOTE = RE.SAP_DELIVERY_NOTE\n" +
            "WHERE OD.SAP_DELIVERY_NOTE = #{sapDeliveryNote} ")
    Map<String, Object> dataHead(@Param("sapDeliveryNote") String sapDeliveryNote,
                                 @Param("postingDate") String postingDate,
                                 @Param("userId") String userId);

    /** 转储收货，按应收收货提交SAP行项目结构 */
    @Select("SELECT ODD.SAP_DELIVERY_NOTE AS \"vbelnIm\",\n" +
            "       ODD.SAP_DELIVERY_NOTE_ITEM AS \"vbelpIm\",\n" +
            "       ODD.MATERIAL_NO AS \"matnr\",\n" +
            "       TO_CHAR(ODD.RECEIVABLE_QTY_BASIC_UNIT) AS \"bstmg\",\n" +
            "       ODD.BASIC_UNIT AS \"bstme\",\n" +
            "       ODD.WERKS_RECEIVE AS \"werks\",\n" +
            "       ODD.WHS_LOCATION_CODE_RECEIVE AS \"lgort\",\n" +
            "       NVL(ODD.REMARKS, ' ') AS \"sgtxt\",\n" +
            "       ODD.SAP_PURCHASE_ORDER AS \"ebeln\",\n" +
            "       ODD.SAP_PURCHASE_ORDER_ITEM AS \"ebelp\"\n" +
            "FROM OVERSEA_DN_DTL ODD\n" +
            "WHERE ODD.SAP_DELIVERY_NOTE = #{sapDeliveryNote} AND ODD.RECEIVABLE_QTY_BASIC_UNIT > 0\n" +
            "ORDER BY TO_NUMBER(ODD.SAP_DELIVERY_NOTE_ITEM)")
    List<Map<String, Object>> dataItems(@Param("sapDeliveryNote") String sapDeliveryNote);

    /** 转储收货，02——本地采购，按实收收货提交SAP行项目结构 */
    @Select("SELECT ODD.SAP_DELIVERY_NOTE AS \"vbelnIm\",\n" +
            "       ODD.SAP_DELIVERY_NOTE_ITEM AS \"vbelpIm\",\n" +
            "       ODD.MATERIAL_NO AS \"matnr\",\n" +
            "       TO_CHAR(ODD.ACTUAL_QTY_BASIC_UNIT) AS \"bstmg\",\n" +
            "       ODD.BASIC_UNIT AS \"bstme\",\n" +
            "       ODD.WERKS_RECEIVE AS \"werks\",\n" +
            "       ODD.WHS_LOCATION_CODE_RECEIVE AS \"lgort\",\n" +
            "       NVL(ODD.REMARKS, ' ') AS \"sgtxt\",\n" +
            "       ODD.SAP_PURCHASE_ORDER AS \"ebeln\",\n" +
            "       ODD.SAP_PURCHASE_ORDER_ITEM AS \"ebelp\"\n" +
            "FROM OVERSEA_DN_DTL ODD\n" +
            "WHERE ODD.SAP_DELIVERY_NOTE = #{sapDeliveryNote} AND ODD.ACTUAL_QTY_BASIC_UNIT > 0\n" +
            "ORDER BY TO_NUMBER(ODD.SAP_DELIVERY_NOTE_ITEM)")
    List<Map<String, Object>> actualDataItems(@Param("sapDeliveryNote") String sapDeliveryNote);

    /** 转储收货，少收提交SAP表头结构 */
    @Select("SELECT REPLACE(OD.ARRIVAL_DATE, '-', '') AS \"bldat\",\n" +
            "       REPLACE(#{underPostingDate}, '-', '') AS \"budat\",\n" +
            "       OD.WERKS_RECEIVE AS \"werks\",\n" +
            "       OD.WHS_LOCATION_CODE_RECEIVE AS \"lgort\",\n" +
            "       'Z15' AS \"bwart\",\n" +
            "       NVL(OD.REMARKS, ' ') AS \"bktxt\"\n" +
            "FROM OVERSEA_DN OD\n" +
            "WHERE OD.SAP_DELIVERY_NOTE = #{sapDeliveryNote} ")
    Map<String, Object> dataHeadAnomalyPost(@Param("sapDeliveryNote") String sapDeliveryNote,
                                            @Param("underPostingDate") String underPostingDate);

    /** 转储收货，少收提交SAP行项目结构 */
    @Select("SELECT ODD.SAP_DELIVERY_NOTE_ITEM AS \"zeile\",\n" +
            "       ODD.MATERIAL_NO AS \"matnr\",\n" +
            "       TO_CHAR(ODD.RECEIVABLE_QTY_BASIC_UNIT - ODD.ACTUAL_QTY_BASIC_UNIT) AS \"menge\",\n" +
            "       ODD.BASIC_UNIT AS \"meins\",\n" +
            "       #{costCenter} AS \"kostl\",\n" +
            "       W.SUPPLIER_NO AS \"lifnr\",\n" +
            "       ODD.WERKS_RECEIVE AS \"werks\",\n" +
            "       ODD.WHS_LOCATION_CODE_RECEIVE AS \"lgort\",\n" +
            "       ODD.REMARKS AS \"sgtxt\"\n" +
            "FROM OVERSEA_DN_DTL ODD\n" +
            "    LEFT JOIN OVERSEA_DN OD ON ODD.SAP_DELIVERY_NOTE = OD.SAP_DELIVERY_NOTE\n" +
            "    LEFT JOIN WERKS W ON ODD.WERKS_RECEIVE = W.WERKS\n" +
            "WHERE ODD.SAP_DELIVERY_NOTE = #{sapDeliveryNote}\n" +
            "  AND ODD.RECEIVABLE_QTY_BASIC_UNIT - ODD.ACTUAL_QTY_BASIC_UNIT > 0\n" +
            "ORDER BY TO_NUMBER(ODD.SAP_DELIVERY_NOTE_ITEM) ")
    List<Map<String, Object>> dataItemsAnomalyPost(@Param("sapDeliveryNote") String sapDeliveryNote,
                                                   @Param("costCenter") String costCenter);

    /** 收货 Z_SPMS_MIGO 撤销接口信息获取 */
    @Select("SELECT OD.SAP_VOUCHER_NUMBER AS \"iMblnr\",\n" +
            "       OD.SAP_VOUCHER_YEAR AS \"iMjahr\",\n" +
            "       REPLACE(#{cancelPostingDate}, '-', '') AS \"iBudat\"\n" +
            "FROM OVERSEA_DN OD\n" +
            "WHERE OD.SAP_DELIVERY_NOTE = #{sapDeliveryNote} ")
    Map<String, Object> dataHeadCancelMigo(@Param("sapDeliveryNote") String sapDeliveryNote,
                                           @Param("cancelPostingDate") String cancelPostingDate);

    /** 少收 Z_SPMS_ANOMALY_POST 撤销接口信息获取 */
    @Select("SELECT OD.UNDER_VOUCHER_NUMBER AS \"iMblnr\",\n" +
            "       OD.UNDER_VOUCHER_YEAR AS \"iMjahr\",\n" +
            "       REPLACE(#{cancelUnderPostingDate}, '-', '') AS \"iBudat\"\n" +
            "FROM OVERSEA_DN OD\n" +
            "WHERE OD.SAP_DELIVERY_NOTE = #{sapDeliveryNote} ")
    Map<String, Object> dataHeadCancelAnomaly(@Param("sapDeliveryNote") String sapDeliveryNote,
                                              @Param("cancelUnderPostingDate") String cancelUnderPostingDate);

}
